#!/usr/bin/env python

"""
:File: dbmanager.py

:Description: DBManager class


History:
2005-01-09:	ADD: lock() e unlock()
2005-01-06:	ADD: del_index()
"""

import cPickle, sys

from os3.types.odict import odict


class DBManagerException ( Exception ): pass

# {{{ DBManagerCursor 
class DBManagerCursor ( object ):

	def __init__ ( self, db, debug = None ):
		"""
		DBManagerCursor ( db, debug = None )

		Creates a new cursor object associated to the ``db`` database instance.
		The ``db`` instance must be created with the ``connect`` method of a
		database driver.
		"""

		self.db = db
		self.debug = debug
		self.cursor = self.db.cursor ()
		self.query_fields = None
		self.remap_fields = None
		self.__row_count = -1

	def execute ( self, query, query_fields = None ):
		"""
		execute ( query, query_fields = None ) -> ?

		Executes the query contained in the string ``query``. The ``query_fields`` parameter
		is a list containing the names of the requested fields in the same order in which
		they are contained in the "select" query. Obviously this parameter is not needed if
		the query is not of the "select" type.

		The method returns anything that the db driver returns.
		"""

		self.query_fields = query_fields
		self._remap_fields ()

		try:
			return self.cursor.execute ( query )

		except:
			if self.debug: self.debug ( 'ERROR: %s: %s\n' % ( sys.exc_info()[0], sys.exc_info()[1] ) )
			return None


	def close ( self ):
		"""
		close ()

		Closes the cursor. After a call to this method the cursor should not be used.
		"""

		self.cursor.close ()


	def fetchone ( self ):
		"""
		fetchone () -> dictionary

		This method returns a record from the set retrieved by the query associated
		to this cursor.

		The record is "formatted" as a dictionary in which the key is the name
		of the field and the value is the field's value.

		When there are no more records the method returns ``None``.

		Note: the dictionary key contains the name of the field spelled exactly
		as in the query; in other words, if the field's name contained a prefix
		(e.g., 't.descr') then the dictionary key contains the same prefix.
		"""

		record_dict = None
		record = self.cursor.fetchone ()

		if record:
			record_dict = dict ( zip ( self.remap_fields, record ) )

			# PATCH: elimino dal dizionario le chiavi NULL del database
			for k in record_dict.keys ():
				if record_dict [ k ] == None: del record_dict [ k ]

		return record_dict


	def fetchall ( self ):
		"""
		fetchall () -> list of dictionaries

		This method returns the list of records retrieved by the query associated
		to this cursor.

		Each record in the list is "formatted" as a dictionary in which the key
		is the name of the field and the value is the field's value.

		Note: the dictionary key contains the name of the field spelled exactly
		as in the query; in other words, if the field's name contained a prefix
		(e.g., 't.descr') then the dictionary key contains the same prefix.
		"""

		record_list = []

		records = self.cursor.fetchall ()

		if len ( records ):
			record_list = [ dict ( zip ( self.remap_fields, rec ) ) for rec in records ]

		return record_list


	def __iter__ ( self ):
		"""
		__iter__ () -> DBManagerCursor

		Returns the iterator associated with this object.
		"""

		return self


	def next ( self ):
		"""
		next () -> dictionary

		Implements the iterator protocol. Each iteration returns the next record
		from the set retrieved by the query. The structure of each record is the
		same as is described in the ``fetchone()`` method.
		"""

		rec = self.fetchone ()
		if not rec: raise StopIteration
		return rec

	def rowcount ( self ):
		if self.__row_count == -1: return self.cursor.rowcount

		return self.__row_count

	def _remap_fields ( self ):
		"""
		_remap_fields ()
		"""

		self.remap_fields = []

		if not self.query_fields: return

		for field in self.query_fields:
			self.remap_fields.append ( self._remap_field ( field ) )

	def _remap_field ( self, field ):
		p = field.lower().find ( ' as ' )
		if p > 0:
			return ( field [ p+4 : ] )
		else:
			p = field.find ( '.' )
			if p > 0:
				return ( field [ p +1 : ] )
			else:
				return ( field )
		

#end class DBManagerCursor
# }}}
# {{{ DBManagerPlugin
class DBManagerPlugin ( object ):
	def __init__ ( self, debug ):
		self._dbmodule = None
		self.debug = debug

	def connect ( self, ** kwargs ):
		return self._dbmodule.connect ( **kwargs )

	def filter_def ( self, txt ):
		"""
			filter_def ( txt ) -> str

			filtra le definizioni SQL
		"""
		return txt

	def insert_id ( self, cur, field_name, table_name ):
		"""
			insert_id ( cur ) -> int

			Restituisce l'ultimo ID inserito.
		"""
		return 0

	def table_list ( self, cur ):
		"""
			table_list ( cur ) -> list

			Restituisce la lista delle tabelle
		"""
		return None

	def field_names ( self, table_name, cur ):
		"""
			field_names ( table_name, cur ) -> list

			Restituisce una lista dei nomi di campo di una tabella
		"""
		return None

	def field_info ( self, table_name, field_name, cur ):
		"""
			field_info ( table_name, field_name, cur ) -> list

			Restituisce una lista con la documentazione del campo ``field_name``
		"""
		return None
# }}}
# {{{ DBManagerPlugin_mysql
class DBManagerPlugin_mysql ( DBManagerPlugin ):
	def __init__ ( self, debug ):
		super ( DBManagerPlugin_mysql, self ).__init__ ( debug )
		self._dbmodule = __import__ ( 'MySQLdb', globals (),  locals (), [] )

	def table_list ( self, cur ):
		cur.execute ( 'show tables' )
		return cur.fetchall ()

	def field_info ( self, table_name, field_name, cur ):
		try:
			cur.execute ( "desc %s" % table_name )

		except self._dbmodule.ProgrammingError:
			raise DBManagerException, "Table %s doesn't exist" % table_name

		return cur.fetchall ()


	def field_names ( self, table_name, cur ):
		try:
			cur.execute ( "desc %s" % table_name )
		except self._dbmodule.ProgrammingError:
			raise DBManagerException, "Table %s doesn't exist" % table_name

		res = []

		for f in cur.fetchall (): res.append ( str ( f [ 0 ] ) )

		if len ( res ) == 0: return None
		return res


	def insert_id ( self, cur, field_name, table_name ):
		try:
			lastid = cur.lastrowid

		except AttributeError:
			lastid = cur.insert_id ()

		return lastid


	def table_create ( self, table_name, table, cur ):
		"""
		table_create ( table_name, table, cur ) -> ?

		Creates (in the db) a table previously defined with the ``table_add()`` method.

		The method returns anything that the db driver returns.
		"""

		fielddefs = table [ 0 ]
		indexdefs = table [ 1 ]

		fields = ', '.join ( [ '%s %s' % ( f, self.filter_def ( d ) ) for ( f, d ) in fielddefs.items () ] )

		query = 'create table %s ( %s ' % ( table_name, fields )

		if indexdefs:
			indexes = ', '.join ( [ '%s ( %s )' % ( i, f ) for ( f, i ) in indexdefs.items () ] )
			query = '%s, %s' % ( query, indexes )

		query = query + ' )'

		if self.debug: self.debug ( "****** CREATE: " + query )

		return cur.execute ( query )

	def add_field ( self, table_name, field_name, kind, size, cur ):
		"""
		add_field ( table_name, field_name, kind, size, cur ) -> ?

		Adds a new field to the table ``table_name``, called ``field_name`` and of type ``kind``.
		If ``size`` is not 0, it will be used in field definition.
		"""

		query = "ALTER TABLE %s ADD %s %s" % ( table_name, field_name, kind )
		if size: query += "(%s)" % size

		return cur.execute ( query )

	def del_field ( self, table_name, field_name, cur ):
		"""
		del_field ( table_name, field_name, cur ) -> ?
		"""
		return cur.execute ( "ALTER TABLE %s DROP %s" % ( table_name, field_name ) )

	def add_index ( self, table_name, field_name, kind, cur ):
		"""
		add_index ( table_name, field_name, kind, cur ) -> ?
		"""
		if self.debug: self.debug ( "ALTER ADD INDEX: %s - %s %s\n" % ( table_name, kind, field_name ) )
	
		return cur.execute ( "ALTER TABLE %s ADD %s (%s)" % ( table_name, kind, field_name ) )

	def del_index ( self, table_name, index_name, cur ):
		"""
		del_index ( table_name, index_name, cur ) -> ?
		"""
		if self.debug: self.debug ( "ALTER DEL INDEX: %s - %s\n" % ( table_name, index_name ) )
	
		try:
			return cur.execute ( "ALTER TABLE %s DROP INDEX %s" % ( table_name, index_name ) )
		except:
			return 0

	def change_field ( self, table_name, field_name, new_name, new_kind, new_size, cur ):
		"""
		change_field ( table_name, field_name, new_name, new_kind, new_size, cur )
		"""
		query = "ALTER TABLE %s CHANGE %s %s %s" % ( table_name, field_name, new_name, new_kind )
		if new_size: query += "(%s)" % new_size
	
		return cur.execute ( query )

	def lock ( self, read, write, cur ):
		"""
		lock ( read, write, cur )

		Locks tables for read / write operations.
		"""
		res = 'LOCK TABLES '
		if read:
			res += ' READ, '.join ( read ) + ' READ'
			if write: res += ','

		if write:
			res += ' WRITE, '.join ( write ) + ' WRITE'

		if self.debug: self.debug ( "LOCKS: %s\n" % res )

		return cur.execute ( res )

	def unlock ( self, tables, cur ):
		return cur.execute ( "UNLOCK TABLES" )
		

# }}}
# {{{ DBManagerPlugin_postgresql
class DBManagerPlugin_postgresql ( DBManagerPlugin ):
	def __init__ ( self, debug ):
		super ( DBManagerPlugin_postgresql, self ).__init__ ( debug )
		import pyPgSQL.PgSQL
		self._dbmodule = pyPgSQL.PgSQL


	def connect ( self, **kwargs ):
		kwargs["host"] = kwargs.get ( 'host', '' )
		kwargs["passwd"] = kwargs.get ( 'passwd', '' )
		kwargs["port"] = kwargs.get ( 'port', '' )
		kwargs["user"] = kwargs.get ( 'user', '' )
		kwargs["db"] = kwargs.get ( 'db', '' )
		
		conn_string = "%(host)s:%(port)s:%(db)s:%(user)s:%(passwd)s::" % kwargs
		conn = self._dbmodule.connect ( conn_string )
		conn.autocommit = 1
		return conn


	def filter_def ( self, txt ):
		
		auto_increment = 0
		
		pos = txt.lower().find ( "auto_increment" )
		if pos >= 0: auto_increment = 1

		txt = txt.split()

		if auto_increment:
			txt[0] = "SERIAL"

			#rimuovo AUTO_INCREMENT
			l = len(txt)
			while l > 0:
				l -= 1
				if txt[l].lower() == "auto_increment":
					del txt[l]
					break
				
		if txt[0].lower() == 'datetime': txt[0] = "TIMESTAMP"
		if txt[0].lower() == 'bool': txt[0] = "INT"
		if txt[0].lower() == 'boolean': txt[0] = "INT"

		return ' '.join ( txt )


	def table_list ( self, cur ):
		cur.execute ( "SELECT tablename FROM pg_tables WHERE schemaname = 'public'" )
		return cur.fetchall ()

	
	def field_info ( self, table_name, field_name, cur ):
		try:
			sql = "SELECT pg_attribute.attname, pg_type.typname, pg_attribute.attlen, pg_attribute.atttypmod - 4 AS attvarlen, "
			sql += "pg_attribute.attnotnull, pg_attrdef.adsrc AS def_val, "
			sql += "pg_index.indkey, pg_index.indisprimary, pg_index.indisunique FROM pg_class, pg_type, pg_attribute "
			sql += "LEFT JOIN pg_index ON ( pg_attribute.attnum = pg_index.indkey[0] AND pg_attribute.attrelid = pg_index.indrelid ) "
			sql += "LEFT JOIN pg_attrdef ON ( pg_attribute.attnum = pg_attrdef.adnum AND pg_attribute.attrelid = pg_attrdef.adrelid ) "
			sql += "WHERE pg_attribute.attrelid = pg_class.relfilenode AND pg_attribute.attnum > 0 AND "
			sql += "pg_type.typelem = pg_attribute.atttypid AND pg_class.relname = '%s'" % table_name
			if field_name != '*': sql += " AND pg_attribute.attname = '%s'" % field_name
			cur.execute ( sql )
		except self._dbmodule.ProgrammingError:
			raise DBManagerException, "Table %s doesn't exist ot field %s doesn't exist" % ( table_name, field_name )

		res = []
		for field in cur.fetchall ():

			#get field length
			len = int(field["attlen"])
			if len < 0: len = int(field["attvarlen"])

			key = ""
			if field["indkey"]:
				if field["indisprimary"]: key = "PRI"
				elif field["indisunique"]: key = "UNI"
				else: key = "MUL"

			if field["attnotnull"]: allow_null = False
			else: allow_null = True

			extras = ""

			field_name = str ( field["attname"] )
			
			if field["def_val"]:
				default_value = str ( field["def_val"] )
				if default_value == "nextval('public.%s_%s_seq'::text)" % ( table_name, field_name ):
					default_value = None
					extras = "auto_increment"
			else:
				default_value = None
			
			flist = [
					field_name,					#name
					str ( field["typname"] ) + "(%d)" % len,	#length
					allow_null,					#null?
					key,						#key type
					default_value,					#default value
					extras						#extras
				]

			res.append ( flist )

		return res


	def field_names ( self, table_name, cur ):
		try:
			sql = "SELECT pg_attribute.attname FROM pg_class, pg_attribute WHERE pg_class.relfilenode = pg_attribute.attrelid "
			sql += "AND pg_attribute.attnum > 0 AND pg_class.relname = '%s'" % table_name
			cur.execute ( sql )
		except self._dbmodule.ProgrammingError:
			raise DBManagerException, "Table %s doesn't exist" % table_name

		res = []

		for f in cur.fetchall (): res.append ( str ( f [ 0 ] ) )

		if len ( res ) == 0: return None
		return res


	def insert_id ( self, cur, field_name, table_name ):
		try:
			cur.execute ( "SELECT currval('%s_%s_seq') AS id" % ( table_name, field_name ) )
		except self._dbmodule.ProgrammingError:
			raise DBManagerException, "Table %s doesn't exist ot field %s doesn't exist" % ( table_name, field_name )

		res = cur.fetchone()
		if not res: return None
		
		res = int ( res['id'] )

		return res

	def table_create ( self, table_name, table, cur ):
		"""
		table_create ( table_name, table, cur ) -> ?

		Creates (in the db) a table previously defined with the ``table_add()`` method.

		The method returns anything that the db driver returns.
		"""

		fielddefs = table [ 0 ]
		indexdefs = table [ 1 ]

		fields = ', '.join ( [ '%s %s' % ( f, self.filter_def ( d ) ) for ( f, d ) in fielddefs.items () ] )

		query = 'create table %s ( %s )' % ( table_name, fields )
		if self.debug: self.debug ( "****** CREATE TABLE: %s\n" % query )
		cur.execute ( query )

		if indexdefs:
			for ( f, i ) in indexdefs.items():
				# i == UNIQUE / INDEX
				# f == nome campo ( es. 'uid', oppure "uid, gid" )
				f2 = f.replace ( " ", "" ).replace ( ",", "_" )
				if i == 'UNIQUE': unique = i
				else: unique = ''

				query = "CREATE %s INDEX %s ON %s ( %s )" % ( unique, f2, table_name, f )
				cur.execute ( query )
# }}}
# {{{ DBManagerPlugin_sqlserver
class DBManagerPlugin_sqlserver ( DBManagerPlugin ):
	def __init__ ( self, debug ):
		super ( DBManagerPlugin_sqlserver, self ).__init__ ( debug )
		self._dbmodule = __import__ ( 'adodbapi', globals (),  locals (), [] )

	def connect ( self, **kwargs ):
		conn_string = "driver={SQL Server};server=%(host)s;uid=%(user)s;pwd=%(passwd)s;database=%(db)s" % kwargs
		conn = self._dbmodule.connect ( conn_string )
		return conn

	def table_list ( self, cur ):
		cur.execute ( 'sp_tables @table_type = "\'TABLE\'"' )
		retval = cur.fetchall ()
		return retval

	def field_info ( self, table_name, field_name, cur ):
		try:
			cur.execute ( "sp_columns @table_name = '%s'" % table_name )
		except self._dbmodule.ProgrammingError:
			raise DBManagerException, "Table %s doesn't exist" % table_name

		retval = cur.fetchall ()
		return retval


	def field_names ( self, table_name, cur ):
		try:
			cur.execute ( "sp_columns @table_name = '%s'" % table_name )
		except self._dbmodule.ProgrammingError:
			raise DBManagerException, "Table %s doesn't exist" % table_name

		retval = cur.fetchall ()

		if not retval: retval = []
		retval = [ f[3] for f in retval ]
		return retval


	def insert_id ( self, cur, field_name, table_name ):
		#TODO
		return cur.insert_id()

	def filter_def ( self, txt ):
		
		auto_increment = 0
		
		pos = txt.lower().find ( "auto_increment" )
		if pos >= 0: auto_increment = 1

		txt = txt.split()

		if auto_increment:
			#modifico AUTO_INCREMENT
			l = len(txt)
			while l > 0:
				l -= 1
				if txt[l].lower() == "auto_increment":
					txt[l] = "IDENTITY"
					break

		if txt[0].lower() == 'bool': txt[0] = "TINYINT"
		if txt[0].lower() == 'boolean': txt[0] = "TINYINT"

		return ' '.join ( txt )

	def table_create ( self, table_name, table, cur ):
		"""
		table_create ( table_name, table, cur ) -> ?

		Creates (in the db) a table previously defined with the ``table_add()`` method.

		The method returns anything that the db driver returns.
		"""

		fielddefs = table [ 0 ]
		indexdefs = table [ 1 ]

		fields = ', '.join ( [ '%s %s' % ( f, self.filter_def ( d ) ) for ( f, d ) in fielddefs.items () ] )

		query = 'create table %s ( %s )' % ( table_name, fields )
		if self.debug: self.debug ( "****** CREATE: " + query )	
		cur.execute ( query )
		
		if indexdefs:
			for ( f, i ) in indexdefs.items():
				# i == UNIQUE / INDEX
				# f == nome campo ( es. 'uid', oppure "uid, gid" )
				f2 = f.replace ( " ", "" ).replace ( ",", "_" )
				if i == 'UNIQUE': unique = i
				else: unique = ''

				query = "CREATE %s INDEX %s ON %s ( %s )" % ( unique, f2, table_name, f )
				cur.execute ( query )
# }}}
# {{{ DBManagerPlugin_sqlite
class DBManagerPlugin_sqlite ( DBManagerPlugin ):
	def __init__ ( self, debug ):
		super ( DBManagerPlugin_sqlite, self ).__init__ ( debug )
		self._dbmodule = __import__ ( 'sqlite', globals (),  locals (), [] )

	def connect ( self, **kwargs ):
		conn_string = "%(db)s" % kwargs
		conn = self._dbmodule.connect ( conn_string, autocommit = 1 )
		return conn

	def table_list ( self, cur ):
		cur.execute ( "SELECT name FROM sqlite_master WHERE type = 'table'" )
		return cur.fetchall ()

	def field_info ( self, table_name, field_name, cur ):
		return []


	def field_names ( self, table_name, cur ):
		return []


	def insert_id ( self, cur, field_name, table_name ):
		cur.execute ( "SELECT last_insert_rowid()" )
		last_id = cur.fetchone()
		return int ( last_id[0] )
		

	def filter_def ( self, txt ):
		
		auto_increment = 0
		
		pos = txt.lower().find ( "auto_increment" )
		if pos >= 0: auto_increment = 1

		if auto_increment: txt = "INTEGER PRIMARY KEY"

		return txt

	def table_create ( self, table_name, table, cur ):
		"""
		table_create ( table_name, table, cur ) -> ?

		Creates (in the db) a table previously defined with the ``table_add()`` method.

		The method returns anything that the db driver returns.
		"""

		fielddefs = table [ 0 ]
		indexdefs = table [ 1 ]

		fields = ', '.join ( [ '%s %s' % ( f, self.filter_def ( d ) ) for ( f, d ) in fielddefs.items () ] )

		query = 'create table %s ( %s )' % ( table_name, fields )
		if self.debug: self.debug ( "****** CREATE: " + query )	
		cur.execute ( query )
		
		if indexdefs:
			for ( f, i ) in indexdefs.items():
				# i == UNIQUE / INDEX
				# f == nome campo ( es. 'uid', oppure "uid, gid" )
				f2 = f.replace ( " ", "" ).replace ( ",", "_" )
				if i == 'UNIQUE': unique = i
				else: unique = ''

				query = "CREATE %s INDEX %s ON %s ( %s )" % ( unique, f2, table_name, f )
				cur.execute ( query )
# }}}

class DBManager ( object ):
	# db types
	DBM_TYPE_MYSQL      = 1
	DBM_TYPE_ORACLE     = 2
	DBM_TYPE_POSTGRESQL = 3
	DBM_TYPE_SQLSERVER  = 4
	DBM_TYPE_FULCRUM    = 5
	DBM_TYPE_SQLITE	    = 6

		     # Tipo               	     name		do_rowcount
	db_types = { 	DBM_TYPE_MYSQL  	:  ( "mysql", 		0 ),
		  	DBM_TYPE_ORACLE 	:  ( "oracle",		0 ),
			DBM_TYPE_POSTGRESQL 	:  ( "postgresql",	1 ),
			DBM_TYPE_SQLSERVER	:  ( "sqlserver",	0 ),
			DBM_TYPE_FULCRUM	:  ( "fulcrum",		0 ),
			DBM_TYPE_SQLITE		:  ( "sqlite",		0 )
		   }

	# {{{ __init__ ( dbtype, debug = None, aspes = None )
	def __init__ ( self, dbtype, debug = None, aspes = None ):
		"""
		DBManager ( dbtype )

		Initializes the DBManager instance. The ``dbtype`` parameter is one of:

		- DBManager.DBM_TYPE_MYSQL
		- DBManager.DBM_TYPE_ORACLE
		"""


		self.dbtype = dbtype
		self.tables = {}
		self.relations = {}
		self.table_prefix = ''
		self.db = None
		self.cursor = None
		self.aspes  = aspes
		self.last_insert_table = None
		self.debug = debug
		self.aspes = aspes
	# }}}
	# {{{ _add_table_prefix ( table )
	def _add_table_prefix ( self, table ):
		"""
		_add_table_prefix ( table ) -> string

		Adds to the table name ``table`` the prefix previously set with the ``set_prefix()``
		method; if there's no prefix to add the original table name is returned.

		If table name starts with a ``*`` char, the prefix will not be added.
		"""

		if table [ 0 ] == '*': return table [ 1: ]

		if self.table_prefix != '':
			return '%s_%s' % ( self.table_prefix, table.strip () )

		else:
			return table
	# }}}
	# {{{ _escape_string ( s )
	def _escape_string ( self, s ):
		"""
		_escape_string ( s ) -> string

		Returns the string ``s`` properly escaped to be inserted in a SQL query. The
		string is also surrounded by single quotes.

		If the input string starts with a ``*``, the string will *not* be escaped.
		"""

		try:
			if ( s [ 0 ] == '*' ): return "%s" % s [ 1: ]
		except:
			pass

		try:
			val = str ( s ).replace ( "'", "''" )
		except AttributeError:
			val = s


		return "'%s'" % val
	# }}}
	# {{{ set_prefix ( prefix )
	def set_prefix ( self, prefix ):
		"""
		set_prefix ( prefix )

		Sets the prefix to be added to all table names before a query
		is executed. For example, if the prefix is "pm" and a table is called
		"projects" then its name becomes "pm_projects".
		"""

		self.table_prefix = prefix
	# }}}
	# {{{ table_add ( name, fields, indexes = None )
	def table_add ( self, name, fields, indexes = None ):
		"""
		table_add ( name, fields, indexes = None )

		Add the definition of a table to the DBManager instance. The table has
		name ``name`` and its fields are contained in the ``fields`` dictionary.

		The ``fields`` dictionary has the following structure:

		- the key is the name of the field;
		- the value is everything that usually follows the name of the field in a
		  "create" SQL statement.

		Example of calling this method::

		  table_add ( 'category', ( ( 'id', 'int not null auto_increment primary key' ),
		                            ( 'name', 'varchar ( 64 )' ),
		                            ( 'descr', 'varchar ( 255 )' ) ) )

		If the table with name ``name`` exists its definition will be replaced
		with the new one.
		"""

		if self.debug: self.debug ( "DBManager: Add Table: %s\n" % name )

		self.tables [ name ] = ( odict ( fields ), odict ( indexes ) )
	# }}}
	# {{{ table_join ( relation )
	def table_join ( self, relation ):
		"""
		table_join ( relation )

		Specifies a relation between two tables. The ``relation`` dictionary should
		contain two keys, one for each table:

		- the key is the name of the table;
		- the value is the name of the field used to relate the table to the other
		  table.

		Example of calling this method::

		  table_join ( { 'projects': 'id', 'tasks': 'project_id' } )

		If a relation between the two tables exists it will be replaced with the
		new one.
		"""

		condition = ' = '.join ( [ '%s.%s' % ( a, b ) for ( a, b ) in relation.iteritems () ] )
		tables = tuple ( relation.keys () )
		self.relations [ tables ] = condition
	# }}}
	# {{{ connect ( ** kwargs )
	def connect ( self, **kwargs ):
		"""
		connect ( **kwargs )

		Connects to the database. The type of database used depends on the ``dbtype``
		parameter passed to the DBManager ``__init__`` method.

		The keyword arguments may vary depending on the db driver type, but
		they usually are:

		- host: host where the db server "lives";
		- user: name used to log in;
		- passwd: password to log in;
		- db: name of the db to connect to.
		"""

		if self.dbtype == self.DBM_TYPE_MYSQL:
			self._dbmodule = DBManagerPlugin_mysql ( self.debug )
		elif self.dbtype == self.DBM_TYPE_POSTGRESQL:
			self._dbmodule = DBManagerPlugin_postgresql ( self.debug )
		elif self.dbtype == self.DBM_TYPE_SQLSERVER:
			self._dbmodule = DBManagerPlugin_sqlserver ( self.debug )
		elif self.dbtype == self.DBM_TYPE_SQLITE:
			self._dbmodule = DBManagerPlugin_sqlite ( self.debug )
		else:
			raise DBManagerException, 'DB type not supported'

		if self.debug: self.debug ( "CONNECT: host: %(host)s - user: %(user)s - db: %(db)s\n" % kwargs )

		self.db = self._dbmodule.connect ( **kwargs )
		self.cursor = self.db.cursor ()
	# }}}
	# {{{ close ()
	def close ( self ):
		"""
		close ()

		Closes the connection to the database.
		"""

		self.cursor.close ()
		self.db.close ()
	# }}}
	# {{{ select ( tables, fields, conds = '', joins = '', extras = '', perms = None, distinct = 0 )
	def select ( self, tables, fields, conds = '', joins = '', extras = '', perms = None, distinct = 0 ):
		"""
		select ( tables, fields, conds = '', joins = '', extras = '', perms = None, distinct = 0 ) -> DBManagerCursor

		Executes a "select" query on the database. The parameters are:

		- tables: list of table names (maybe containing the "as" keyword followed by
		  the alias); if there's only one table it can be passed as a simple string
		  (without the surrounding list);
		- fields: list of fields to be retrieved; if there's only one field it can
		  be passed as a simple string (without the surrounding list);
		- conds: string with the conditions to be used after the "where" keyword of
		  the query;
		- joins: string with the conditions that specify the join among the tables
		  involved;
		- extras: string with extra stuff to be added to the end of the query..
		- perms:  table prefix on which to apply permissions.
		- distinct: flag T/F. If T the select will contain the DISTINCT keyword

		The method returns a DBManagerCursor instance containing the query results.
		"""

		cursor = DBManagerCursor ( self.db, self.debug )

		# if the table is a string, convert it to a list of (one) table(s).
		if not isinstance ( tables, list ): tables = [ tables ]
		# if the field is a string, convert it to a list of (one) field(s).
		if isinstance ( fields, str ) and fields == '*': fields = self.field_names ( tables [ 0 ].split() [ 0 ] )
		if not isinstance ( fields, list ): fields = [ fields ]

		if self.table_prefix != '':
			from_clause = ', '.join ( [ self._add_table_prefix ( t ) for t in tables ] )
		else:
			from_clause = ', '.join ( tables )

		select_clause = ', '.join ( fields )

		conditions = conds

		if len ( joins ):
			if len ( conditions ):
				conditions = '%s and %s' % ( joins, conditions )

			else:
				conditions = joins

		if perms:
			if len ( conditions ): conditions += ' and '
			conditions += ' (  ( ( %s.uid = %d ) and ( ( %s.perms & 32 ) <> 0 ) ) '  % ( perms, self.aspes.uid, perms ) 
			conditions += '  or ( ( %s.gid IN ( %s ) ) and ( ( %s.perms & 8 ) <> 0 ) ' % ( perms, self.aspes.groups, perms )
			conditions += '  or ( ( %s.perms & 2 ) <> 0 ) ) )' % perms

		if distinct:
			dist = " DISTINCT "
		else:
			dist = ''

		query = 'select %s %s from %s %s %s %s' % ( dist, select_clause, from_clause, ( '', 'where' ) [ len ( conditions ) != 0 ], conditions, extras )

		if self.debug: self.debug ( "SELECT: %s\n" % query )

		try:
			# Check if we need to do the select count(*)
			if self.db_types [ self.dbtype ] [ 1 ]:
				count_query = 'select %s count(*) from %s %s %s %s' % ( dist, 
					       from_clause, ( '', 'where' ) [ len ( conditions ) != 0 ], conditions, extras )

				cursor.execute ( count_query )

				vals = cursor.cursor.fetchone ()
				__row_count = vals [ 0 ]

			cursor.execute ( query, fields )

			# Check if we need to do the select count(*)
			if self.db_types [ self.dbtype ] [ 1 ]: cursor.__row_count = __row_count

			rowcount = cursor.rowcount()

			if not rowcount or rowcount <= 0:
				cursor.close ()
				return None

			return cursor

		except:
			cursor.close ()
			if self.debug: self.debug ( "ERROR: SQL: %s\n" % query )
			raise DBManagerException, '%s: %s' % ( sys.exc_info()[0], sys.exc_info()[1] )
	# }}}
	# {{{ count ( tables, fields, conds = '', joins = '', extras = '', perms = None, distinct = 0 )
	def count ( self, tables, fields, conds = '', joins = '', extras = '', perms = None, distinct = 0 ):
		"""
		count ( tables, fields, conds = '', joins = '', extras = '', perms = None, distinct = 0 ) -> int

		Executes a "select" query on the database.
		
		The method returns the records count.
		"""

		if isinstance ( fields, str ): fields = [ fields ]

		rs = self.select ( tables, "COUNT(" + ', '.join ( fields ) + ") AS record_count", conds = conds, joins = joins,
			extras = extras, perms = perms, distinct = distinct )
		if rs:
			row = rs.fetchone()
			retval = row["record_count"]
			rs.close()
		else:
			retval = 0

		return retval

	#}}}
	# {{{ single_row ( tables, fields, conds = '', joins = '', extras = '', perms = None )
	def single_row ( self, tables, fields, conds = '', joins = '', extras = '', perms = None ):
		"""
		single_row ( tables, fields, conds = '', joins = '', extras = '' ) -> dictionary

		This is a shortcut function to query one single row from a database.
		This method executes the query by calling ``select()`` and then retrieves
		the row database by calling ``fetchone()`` on the returned cursor.
		It also frees the cursor before returning the dictionary.

		For parameters explanation, please refer to the ``select()`` documentation.
		"""

		cur = self.select ( tables, fields, conds, joins, extras, perms = perms )
		if not cur: return None

		row = cur.fetchone ()
		cur.close ()

		return row
	# }}}
	# {{{ all_rows ( tables, fields, conds = '', joins = '', extras = '', perms = None )
	def all_rows ( self, tables, fields, conds = '', joins = '', extras = '', perms = None ):
		"""
		all_rows ( tables, fields, conds = '', joins = '', extras = '' ) -> list of dictionaries

		This is a shortcut function to query all rows from a database.
		This method executes the query by calling ``select()`` and then retrieves
		the row database by calling ``fetchall()`` on the returned cursor.
		It also frees the cursor before returning the dictionary.

		For parameters explanation, please refer to the ``select()`` documentation.
		"""

		cur = self.select ( tables, fields, conds, joins, extras, perms = perms )
		if not cur: return None

		rows = cur.fetchall ()
		cur.close ()

		return rows
	# }}}
	# {{{ insert ( table, data )
	def insert ( self, table, data ):
		"""
		insert ( table, data ) -> ?

		Executes an "insert" query on the database. The parameters are:

		- table: name of the table to be modified;
		- data: dictionary containing the data to be written to the table. The key
		  of the dictionary is the name of the field and the value is the value to
		  be written in the field.

		The method returns anything that the db driver returns.
		"""

		fields = ', '.join ( data.iterkeys () )
		values = ', '.join ( [ self._escape_string ( v ) for v in data.itervalues () ] )

		table = self._add_table_prefix ( table )

		query = 'insert into %s ( %s ) values ( %s )' % ( table, fields, values )

		if self.debug: self.debug ( 'DBManager: INSERT: %s\n' % query )

		self.last_insert_table = table

		try:
			res = self.cursor.execute ( query )

		except:
			raise DBManagerException, '%s: %s' % ( sys.exc_info () [ 0 ], sys.exc_info () [ 1 ] )

		return res
	# }}}
	# {{{ update ( table, data, conds = None )
	def update ( self, table, data, conds = None ):
		"""
		update ( table, data, conds = None ) -> ?

		Executes an "update" query on the database. The parameters are:

		- table: name of the table to be updated;
		- data: dictionary containing the values to be modified in the table. The key
		  of the dictionary is the name of the field and the value is the value to be
		  written in the field;
		- conds: string with the conditions to be used after the "where" clause of the
		  query.

		The method returns anything that the db driver returns.
		"""

		fields = ', '.join ( [ "%s = %s" % ( f, self._escape_string ( v ) ) for ( f, v ) in data.iteritems () ] )

		table = self._add_table_prefix ( table )

		query = 'update %s set %s %s %s' % ( table, fields, ( '', 'where' ) [ conds != None ], ( '', conds ) [ conds != None ] )

		if self.debug: self.debug ( "UPDATE: %s\n" % query )

		try:
			res = self.cursor.execute ( query )

		except:
			raise DBManagerException, '%s: %s' % ( sys.exc_info () [ 0 ], sys.exc_info () [ 1 ] )

		return res
	# }}}
	# {{{ delete ( table, conds = None )
	def delete ( self, table, conds = None ):
		"""
		delete ( table, conds = None ) -> ?

		Executes a "delete" query on the database. The parameters are:

		- table: name of the table to be affected;
		- conds: string with the conditions to be used after the "where" clause of the
		  query.

		The method returns anything that the db driver returns.
		"""

		table = self._add_table_prefix ( table )

		query = 'delete from %s %s %s' % ( table, ( '', 'where' ) [ conds != None ], ( '', conds ) [ conds != None ] )

		if self.debug: self.debug ( 'DBManager: DELETE: %s\n' % query )

		return self.cursor.execute ( query )
	# }}}
	# {{{ table_create ( table_name )
	def table_create ( self, table_name ):
		"""
		table_create ( table_name ) -> ?

		Creates (in the db) a table previously defined with the ``table_add()`` method.

		The method returns anything that the db driver returns.
		"""

		if self.exists ( table_name ): return

		table = self.tables [ table_name ]
		table_name = self._add_table_prefix ( table_name )

		return self._dbmodule.table_create ( table_name, table, self.cursor )
	# }}}
	# {{{ table_drop ( table_name )
	def table_drop ( self, table_name ):
		"""
		table_drop ( table_name ) -> ?

		Drops a table from the database
		"""
		if self.exists ( table_name ):
			return self.cursor.execute ( "DROP TABLE %s" % self._add_table_prefix ( table_name ) )
	# }}}
	# {{{ table_add_field ( table_name, field_name, kind, size )
        def table_add_field ( self, table_name, field_name, kind, size = 0 ):
                """
                	table_add_field ( table_name, field_name, kind, size = 0 ) -> ?
                """
		table_name = self._add_table_prefix ( table_name )
		return self._dbmodule.add_field ( table_name, field_name, kind, size, self.cursor )
	# }}}
	# {{{ table_del_field ( table_name, field_name )
        def table_del_field ( self, table_name, field_name ):
                """
                	table_del_field ( table_name, field_name ) -> ?
                """
		table_name = self._add_table_prefix ( table_name )
		return self._dbmodule.del_field ( table_name, field_name, self.cursor )
	# }}}
	# {{{ table_add_index ( table_name, field_name, kind )
        def table_add_index ( self, table_name, field_name, kind ):
                """
                	table_add_index ( table_name, field_name, kind ) -> ?
                """
		table_name = self._add_table_prefix ( table_name )
		return self._dbmodule.add_index ( table_name, field_name, kind, self.cursor )
	# }}}
	# {{{ table_del_index ( table_name, field_name, kind )
        def table_del_index ( self, table_name, index_name ):
                """
                	table_del_index ( table_name, index_name ) -> ?
                """
		table_name = self._add_table_prefix ( table_name )
		return self._dbmodule.del_index ( table_name, index_name, self.cursor )
	# }}}
	# {{{ table_change_field ( table_name, field_name, new_name, new_kind, new_size )
        def table_change_field ( self, table_name, field_name, new_name, new_kind, new_size ):
                """
                	table_del_field ( table_name, field_name, new_name, new_kind, new_size ) -> ?
                """
		table_name = self._add_table_prefix ( table_name )
		return self._dbmodule.change_field ( table_name, field_name, new_name, new_kind, new_size, self.cursor )
	# }}}
	# {{{ lock ( read = None, write = None )
        def lock ( self, read = None, write = None ):
                """
                	lock ( read = None, write = None )
                """
		if read and not isinstance ( read, list ): read = [ read ]
		if write and not isinstance ( write, list ): write = [ write ]

		if read:  read = [ self._add_table_prefix ( x ) for x in read ]
		if write: write = [ self._add_table_prefix ( x ) for x in write ]

		return self._dbmodule.lock ( read, write, self.cursor )
	# }}}

	# {{{ unlock ( tables = None )
	def unlock ( self, tables = None ):
		"""
		"""
		if tables and not instance ( tables, list ): tables = [ tables ]

		if tables: tables = [ self._add_table_prefix ( x ) for x in tables ]
		return self._dbmodule.unlock ( tables, self.cursor )
	# }}}



	# {{{ execute ( query )
	def execute ( self, query ):
		"""
		execute ( query ) -> ?

		Executes the query contained in the ``query`` parameter.

		This method should be used to execute "non select" queries because there's
		no way to access the records that the query may have retrieved. To
		execute "select" queries use the ``select()`` method.

		The method returns anything that the db driver returns.
		"""

		if self.debug: self.debug ( 'DBManager: EXECUTE: %s\n' % query )
		
		return self.cursor.execute ( query )
	# }}}
	# {{{ exists ( table_name )
	def exists ( self, table_name ):
		"""
		exists ( table_name ) -> 0 or 1
	
		Checks if ``table_name`` is exists in the current database.
		It returns 1 if the table exists, 0 otherwise.
		"""

		table_name = self._add_table_prefix ( table_name )

		try:
			if self.debug: self.debug ( 'TAB EXISTS: %s\n' % table_name )

			lst = self.table_list ()

			if self.debug: self.debug ( 'EXISTS: %s\n' % lst )
			table_name = table_name.lower()
			for t in lst:
				if t.lower() == table_name: return 1

			return 0
		except:
			return 0
	# }}}
	# {{{ table_list ()
	def table_list ( self ):
		"""
		table_list () -> list of strings

		Returns the list of strings representing the table names.
		"""
		lst = self._dbmodule.table_list ( self.cursor )
		
		return [ t [ 0 ].strip() for t in lst ]

		# La riga qui sotto sbaglia a togliere il prefisso (a volte)
		#return [ t[ 0 ].strip ( '%s_' % self.table_prefix ) for t in lst ]
	# }}}
	# {{{ field_info ( table_name, field_name = '*' )
	def field_info ( self, table_name, field_name = '*' ):
		"""
		field_info ( table_name, field_name ) -> dictionary

		Parameters:
			- ``table_name``: the name of the table being analyzed	
			- ``field_name``: the name of the field you wish to analyze. Use '*' to get all fields inside a table.
		Returns a list of dictionaries containing info about the field ``field_name`` in the table ``table_name``.
		The dictionary returned contains the following keys:

		- name: field name;
		- type: field type;
		- null: "YES" if it can be NULL, otherwise the empty string;
		- key: type of key;
		- default: default value or None;
		- extra: other info (e.g., autoincrement, ecc.)

		If ``table_name`` doesn't exist ``DBManagerException`` is raised.
		If ``field_name`` doesn't exist the method returns ``None``.
		"""
		res = []

		for f in self._dbmodule.field_info ( self._add_table_prefix ( table_name ), field_name, self.cursor ):
			if field_name != '*':
				if f [ 0 ] == field_name:
					res.append ( dict ( zip ( ( 'name', 'type', 'null', 'key', 'default', 'extra' ), f ) ) )
			else:
					res.append ( dict ( zip ( ( 'name', 'type', 'null', 'key', 'default', 'extra' ), f ) ) )
			

		if len ( res ) == 0: return None

		return res
	# }}}
	# {{{ field_names ( table_name )
	def field_names ( self, table_name ):
		"""
		field_names ( table_name )

		Returns a list containing all the field names of the given ``table_name``
		"""
		return self._dbmodule.field_names ( self._add_table_prefix ( table_name ), self.cursor )
	# }}}
	# {{{ insert_id ( field_name = 'id', table_name = None )
	def insert_id ( self, field_name = 'id', table_name = None ):
		"""
		insert_id () -> id

		Returns the id of the last line inserted with the previous ``insert()`` method.
		"""

		if not table_name: table_name = self.last_insert_table

		return self._dbmodule.insert_id ( self.cursor, field_name, table_name )
	# }}}
	# {{{ create_select_list ( tables, fields, conds = '', joins = '', extras = '', sep = ' ', perms = None, distinct = 0 )
	def create_select_list ( self, tables, fields, conds = '', joins = '', extras = '', sep = ' ', perms = None, distinct = 0 ):
		"""
		"""
		cur = self.select ( tables, fields, conds = conds, joins = joins, extras = extras, perms = perms, distinct = distinct )
		if not cur: return None

		lst = []

		for vals in cur:
			res_str = ''
			for field in fields [ 1: ]:
				if len ( res_str ): res_str += sep
				res_str += vals [ cur._remap_field ( field ) ]
				
			lst.append ( ( vals [ cur._remap_field ( fields [ 0 ] ) ], res_str ) )

		cur.close ()

		return lst
	# }}}
	# {{{ pickle ( what )
	def pickle ( self, what ):
                """
                	pickle ( what ) -> string

			Returns a valid SQL string rappresenting the given object ``what``.
                """

                return "*'%s'" % cPickle.dumps( what ).replace ( "'", r"\'" )
	# }}}
	# {{{ unpickle ( what )
        def unpickle ( self, what ):
                """
                	unpickle ( what ) -> object

			Gets a SQL serialized string ``what`` and returns the original unpickled
			object.
                """

                return cPickle.loads( what.replace ( r"\'", "'" ) )
	# }}}



	# {{{ resolve_type ( type )
	def resolve_type ( self, type ):
		"""
		resolve_type ( type ) -> a type constant

		Decodes the ``type`` string and returns its equivalent type as a
		numeric constant. Up to now the association table is as follows:

		======     ===============
		type       constant
		======     ===============
		mysql      DBM_TYPE_MYSQL
		oracle     DBM_TYPE_ORACLE
		======     ===============
		"""
		for key in self.db_types.keys ():
			if self.db_types [ key ] [ 0 ] == type.lower (): return key

		return self.DBM_TYPE_MYSQL
	# }}}

	resolve_type = classmethod ( resolve_type )

#end class DBManager

# {{{ test
if __name__ == '__main__':
# ------
#  TEST
# ------

	def test ():
		dbm = DBManager ( DBManager.DBM_TYPE_POSTGRESQL )

		dbm.set_prefix ( '' )

		# tables

		dbm.table_add ( 'wa_user_2', ( 
			( "id", "INT PRIMARY KEY NOT NULL AUTO_INCREMENT" ),
			( "login", "VARCHAR(20)" ),
			( "passwd", "CHAR(32)" ),
			( "email", "VARCHAR(80)" ),
			( "lang", "CHAR(2)" ),
			( "enabled", "BOOL" ),
			( "theme", "VARCHAR(20)" ),
			( "created", "DATETIME" ),
			( "last_log", "DATETIME" ) ),
		 #indexes
			( ( "login", "UNIQUE" ),
			( "created", "INDEX" ),
			( "enabled", "INDEX" ) )
		)

		dbm.connect ( host = 'localhost', user = 'root', passwd = '', db = 'aspes' )

		#dbm.insert ( 'status', { 'name': 'PIPPO', 'descr': 'PLUTO', 'stype': 0 } )
		#cur = dbm.select ( tables = [ 'projects as p', 'tasks as t' ], fields = [ 'p.name', 't.short_descr', 't.end_date' ], conds = "t.end_date < '2004-03-11'", joins = 'p.id = t.project_id' )
		#for rec in cur:
		#	print rec
		#cur.close ()

		dbm.table_create ( 'wa_user_2' )

		print dbm.table_list ()
		#fields = dbm.field_names ( 'wa_user_2' )
		#print fields
		#for f in fields:
			#print dbm.field_info ( 'wa_user_2', f )

		#print dbm.field_info ( 'wa_user_2', '*' )

		#res =  dbm.select ( 'user as u', [ 'u.login as user_login', 'u.email as user_email' ] )
		#for row in res: print row
		#res.close ()

		dbm.close ()

	test ()

# }}}
